﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Monkey.Warrenty.Bo;
using Monkey.Warrenty.Data.Interfaces;
using Monkey.Warrenty.EntityModel;
using MonkeyWarranty.Models.TicketModels;

namespace Monkey.Warrenty.Data
{
    public class InstantTicketDao : IInstantTicketDao
    {
        #region Properties
        #endregion

        #region Ticket Methods

        public long GetCurrentIdentity()
        {
            long? identity;
            using (var context = new MonkeyWarrantyEntities())
            {
                identity = context.GetIdentity("InstantTickets").SingleOrDefault(); //context.InstantticketGetIdentity().SingleOrDefault();
            }

            return identity.HasValue ? identity.Value : 0;
        }

        public long GetMaxId()
        {
            long maxId = 0;

            using (var context = new MonkeyWarrantyEntities())
            {
                var ticket = context.InstantTickets.OrderByDescending(x => x.Id).FirstOrDefault();
                if (ticket != null) maxId = ticket.Id;
            }

            return maxId;
        }

        public InstantTicket AddTicket(InstantTicket ticket)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                context.InstantTickets.AddObject(ticket);
                context.SaveChanges();
            }

            return ticket;
        }

        public InstantTicket AddTicket(InstantTicket ticket, Customer customer)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                if (customer.Id == 0)
                {
                    context.Customers.AddObject(customer);
                }
                ticket.CustomerId = customer.Id;

                context.InstantTickets.AddObject(ticket);
                context.SaveChanges();
            }

            return ticket;
        }

        public InstantTicket GetById(long id)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                return context.InstantTickets.FirstOrDefault(x => x.Id == id);
            }
        }

        public List<InstantTicketView> GetTicketViews(int startIndex, int count, string sorting, int status)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                IEnumerable<InstantTicketView> query = context.InstantTicketViews;

                //Filters
                if (status > 0)
                {
                    query = query.Where(x => x.StatusId == status);
                }

                // sorting
                if (string.IsNullOrEmpty(sorting) || sorting.Equals("CreatedDate ASC"))
                {
                    query = query.OrderBy(x => x.CreatedDate);
                }
                else if (sorting.Equals("CreatedDate DESC"))
                {
                    query = query.OrderByDescending(x => x.CreatedDate);
                }
                else if (sorting.Equals("Code ASC"))
                {
                    query = query.OrderBy(x => x.Code);
                }
                else if (sorting.Equals("Code DESC"))
                {
                    query = query.OrderByDescending(x => x.Code);
                }
                else if (sorting.Equals("EmployeeName ASC"))
                {
                    query = query.OrderBy(x => x.EmployeeName);
                }
                else if (sorting.Equals("EmployeeName DESC"))
                {
                    query = query.OrderByDescending(x => x.EmployeeName);
                }
                else if (sorting.Equals("CustomerName ASC"))
                {
                    query = query.OrderBy(x => x.CustomerName);
                }
                else if (sorting.Equals("CustomerName DESC"))
                {
                    query = query.OrderByDescending(x => x.CustomerName);
                }

                return count > 0
                           ? query.Skip(startIndex).Take(count).ToList() //Paging
                           : query.ToList(); //No paging
            }
        }

        public int SearchTicketsCount(InstantSearchModel searchModel)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                IEnumerable<InstantTicketView> query = context.InstantTicketViews;

                //Filters
                if (!string.IsNullOrWhiteSpace(searchModel.Code))
                {
                    query = query.Where(x => x.Code.Contains(searchModel.Code));
                }
                if (searchModel.Status.Count() > 0)
                {
                    query = query.Where(x => searchModel.Status.Contains(x.StatusId));
                }
                if (searchModel.CreatedDateFrom.HasValue)
                {
                    query = query.Where(x => x.CreatedDate >= searchModel.CreatedDateFrom);
                }
                if (searchModel.CreatedDateTo.HasValue)
                {
                    query = query.Where(x => x.CreatedDate < searchModel.CreatedDateTo.Value.AddDays(1));
                }

                return query.Count();
            }
        }

        public List<InstantTicketView> SearchTickets(InstantSearchModel searchModel)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                IEnumerable<InstantTicketView> query = context.InstantTicketViews;

                //Filters
                if (!string.IsNullOrWhiteSpace(searchModel.Code))
                {
                    query = query.Where(x => x.Code.Contains(searchModel.Code));
                }
                if (searchModel.Status.Count() > 0)
                {
                    query = query.Where(x => searchModel.Status.Contains(x.StatusId));
                }
                if (searchModel.CreatedDateFrom.HasValue)
                {
                    query = query.Where(x => x.CreatedDate >= searchModel.CreatedDateFrom);
                }
                if (searchModel.CreatedDateTo.HasValue)
                {
                    query = query.Where(x => x.CreatedDate < searchModel.CreatedDateTo.Value.AddDays(1));
                }

                // sorting
                var sorting = searchModel.Sorting;
                if (string.IsNullOrEmpty(sorting) || sorting.Equals("CreatedDate ASC"))
                {
                    query = query.OrderBy(x => x.CreatedDate);
                }
                else if (sorting.Equals("CreatedDate DESC"))
                {
                    query = query.OrderByDescending(x => x.CreatedDate);
                }
                else if (sorting.Equals("Code ASC"))
                {
                    query = query.OrderBy(x => x.Code);
                }
                else if (sorting.Equals("Code DESC"))
                {
                    query = query.OrderByDescending(x => x.Code);
                }
                else if (sorting.Equals("CustomerName ASC"))
                {
                    query = query.OrderBy(x => x.CustomerName);
                }
                else if (sorting.Equals("CustomerName DESC"))
                {
                    query = query.OrderByDescending(x => x.CustomerName);
                }

                return searchModel.PageSize > 0
                           ? query.Skip(searchModel.StartIndex).Take(searchModel.PageSize).ToList() //Paging
                           : query.ToList(); //No paging
            }
        }

        public void UpdateTicket(InstantTicket ticket)
        {
            if (ticket == null || ticket.Id == 0) return;

            using (var context = new MonkeyWarrantyEntities())
            {
                context.InstantTickets.Attach(ticket);
                context.ObjectStateManager.ChangeObjectState(ticket, EntityState.Modified);
                context.SaveChanges();
            }
        }

        public void DeleteTicket(long id)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                var ticket = new InstantTicket { Id = id };
                context.InstantTickets.Attach(ticket);
                context.DeleteObject(ticket);
                context.SaveChanges();
            }
        }

        public int GetTicketCountByFilter(int statusId)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                IEnumerable<InstantTicketView> query = context.InstantTicketViews;

                //Filters
                //if (statusId > 0)
                //{
                //    query = query.Where(x => x.StatusId == statusId);
                //}

                return query.Count(x => x.StatusId == statusId);
            }
        }

        public long CopyToWarraty(long id)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                var result = db.InstantTicket_CopyToWarrantyTicket(id).FirstOrDefault();
                return result == null ? 0 : result.Value;
            }
        }

        #endregion

        #region Ticket Product Methods

        public InstantTicket_Product CreateTicketProduct(InstantTicket_Product ticketProduct)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                context.InstantTickets_Products.AddObject(ticketProduct);
                context.SaveChanges();
            }

            return ticketProduct;
        }

        public List<InstantTicketProductView> GetTicketProducts(long ticketId)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                return context.InstantTicketProductViews.Where(x => x.InstantTicketId == ticketId).ToList();
            }
        }

        public void DeleteTicketProduct(long ticketProductId)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                var ticketProduct = context.InstantTickets_Products.FirstOrDefault(x => x.Id == ticketProductId);
                if (ticketProduct == null) return;
                context.DeleteObject(ticketProduct);
                context.SaveChanges();
            }
        }

        public InstantTicket_Product GetTicketProductById(long id)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                return context.InstantTickets_Products.FirstOrDefault(x => x.Id == id);
            }
        }

        public void UpdateTicketProduct(InstantTicket_Product ticketProduct)
        {
            if (ticketProduct == null || ticketProduct.Id == 0) return;

            using (var context = new MonkeyWarrantyEntities())
            {
                context.InstantTickets_Products.Attach(ticketProduct);
                context.ObjectStateManager.ChangeObjectState(ticketProduct, EntityState.Modified);
                context.SaveChanges();
            }
        }

        public InstantTicket_Service CreateTicketService(InstantTicket_Service ticketService)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                context.InstantTickets_Services.AddObject(ticketService);
                context.SaveChanges();
            }

            return ticketService;
        }

        public List<InstantTicketServiceView> GetTicketServices(long ticketId)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                return context.InstantTicketServiceViews.Where(x => x.InstantTicketId == ticketId).ToList();
            }
        }

        public void DeleteTicketService(long ticketServiceId)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                var ticketService = context.InstantTickets_Services.FirstOrDefault(x => x.Id == ticketServiceId);
                if (ticketService == null) return;
                context.DeleteObject(ticketService);
                context.SaveChanges();
            }
        }

        public InstantTicket_Service GetTicketServiceById(long id)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                return context.InstantTickets_Services.FirstOrDefault(x => x.Id == id);
            }
        }

        public void UpdateTicketService(InstantTicket_Service ticketService)
        {
            if (ticketService == null || ticketService.Id == 0) return;

            using (var context = new MonkeyWarrantyEntities())
            {
                context.InstantTickets_Services.Attach(ticketService);
                context.ObjectStateManager.ChangeObjectState(ticketService, EntityState.Modified);
                context.SaveChanges();
            }
        }

        #endregion

        #region Services

        public int SearchServicesCount(InstantServiceSearchModel searchModel)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                IEnumerable<InstantTicketServiceView> query = db.InstantTicketServiceViews;

                //Filters
                if (!string.IsNullOrWhiteSpace(searchModel.TicketCode))
                {
                    query = query.Where(x => x.InstantTicketCode.Contains(searchModel.TicketCode));
                }
                if (searchModel.Status.Count() > 0)
                {
                    query = query.Where(x => searchModel.Status.Contains(x.InstantServiceStateId));
                }
                if (searchModel.StartDateFrom.HasValue)
                {
                    query = query.Where(x => x.StartDate >= searchModel.StartDateFrom);
                }
                if (searchModel.StartDateTo.HasValue)
                {
                    query = query.Where(x => x.StartDate < searchModel.StartDateTo.Value.AddDays(1));
                }

                return query.Count();
            }
        }

        public List<InstantTicketServiceView> SearchServices(InstantServiceSearchModel searchModel)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                IEnumerable<InstantTicketServiceView> query = db.InstantTicketServiceViews;

                //Filters
                if (!string.IsNullOrWhiteSpace(searchModel.TicketCode))
                {
                    query = query.Where(x => x.InstantTicketCode.Contains(searchModel.TicketCode));
                }
                if (searchModel.Status.Count() > 0)
                {
                    query = query.Where(x => searchModel.Status.Contains(x.InstantServiceStateId));
                }
                if (searchModel.StartDateFrom.HasValue)
                {
                    query = query.Where(x => x.StartDate >= searchModel.StartDateFrom);
                }
                if (searchModel.StartDateTo.HasValue)
                {
                    query = query.Where(x => x.StartDate < searchModel.StartDateTo.Value.AddDays(1));
                }

                // sorting
                var sorting = searchModel.Sorting;
                if (string.IsNullOrEmpty(sorting) || sorting.Equals("StartDateString ASC"))
                {
                    query = query.OrderBy(x => x.StartDate);
                }
                else if (sorting.Equals("StartDateString DESC"))
                {
                    query = query.OrderByDescending(x => x.StartDate);
                }
                else if (sorting.Equals("InstantTicketCode ASC"))
                {
                    query = query.OrderBy(x => x.InstantTicketCode);
                }
                else if (sorting.Equals("InstantTicketCode DESC"))
                {
                    query = query.OrderByDescending(x => x.InstantTicketCode);
                }
                //else if (sorting.Equals("CustomerName ASC"))
                //{
                //    query = query.OrderBy(x => x.CustomerName);
                //}
                //else if (sorting.Equals("CustomerName DESC"))
                //{
                //    query = query.OrderByDescending(x => x.CustomerName);
                //}

                return searchModel.PageSize > 0
                           ? query.Skip(searchModel.StartIndex).Take(searchModel.PageSize).ToList() //Paging
                           : query.ToList(); //No paging
            }
        }

        #endregion

        #region Products

        public int SearchProductsCount(InstantProductSearchModel searchModel)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                IEnumerable<InstantTicketProductView> query = db.InstantTicketProductViews;

                //Filters
                if (!string.IsNullOrWhiteSpace(searchModel.TicketCode))
                {
                    query = query.Where(x => x.InstantTicketCode.Contains(searchModel.TicketCode));
                }
                if (searchModel.Status.Count() > 0)
                {
                    query = query.Where(x => searchModel.Status.Contains(x.InstantProductStateId));
                }
                if (searchModel.StartDateFrom.HasValue)
                {
                    query = query.Where(x => x.StartDate >= searchModel.StartDateFrom);
                }
                if (searchModel.StartDateTo.HasValue)
                {
                    query = query.Where(x => x.StartDate < searchModel.StartDateTo.Value.AddDays(1));
                }

                return query.Count();
            }
        }

        public List<InstantTicketProductView> SearchProducts(InstantProductSearchModel searchModel)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                IEnumerable<InstantTicketProductView> query = db.InstantTicketProductViews;

                //Filters
                if (!string.IsNullOrWhiteSpace(searchModel.TicketCode))
                {
                    query = query.Where(x => x.InstantTicketCode.Contains(searchModel.TicketCode));
                }
                if (searchModel.Status.Count() > 0)
                {
                    query = query.Where(x => searchModel.Status.Contains(x.InstantProductStateId));
                }
                if (searchModel.StartDateFrom.HasValue)
                {
                    query = query.Where(x => x.StartDate >= searchModel.StartDateFrom);
                }
                if (searchModel.StartDateTo.HasValue)
                {
                    query = query.Where(x => x.StartDate < searchModel.StartDateTo.Value.AddDays(1));
                }

                // sorting
                var sorting = searchModel.Sorting;
                if (string.IsNullOrEmpty(sorting) || sorting.Equals("StartDateString ASC"))
                {
                    query = query.OrderBy(x => x.StartDate);
                }
                else if (sorting.Equals("StartDateString DESC"))
                {
                    query = query.OrderByDescending(x => x.StartDate);
                }
                else if (sorting.Equals("InstantTicketCode ASC"))
                {
                    query = query.OrderBy(x => x.InstantTicketCode);
                }
                else if (sorting.Equals("InstantTicketCode DESC"))
                {
                    query = query.OrderByDescending(x => x.InstantTicketCode);
                }
                //else if (sorting.Equals("CustomerName ASC"))
                //{
                //    query = query.OrderBy(x => x.CustomerName);
                //}
                //else if (sorting.Equals("CustomerName DESC"))
                //{
                //    query = query.OrderByDescending(x => x.CustomerName);
                //}

                return searchModel.PageSize > 0
                           ? query.Skip(searchModel.StartIndex).Take(searchModel.PageSize).ToList() //Paging
                           : query.ToList(); //No paging
            }
        }

        public void UpdateTicketServiceStatus(long instantServiceId, int statusId, string description, string note)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                db.InstantTicket_UpdateServiceStatus(instantServiceId, statusId, description, note);
            }
        }

        public List<InstantTicketServiceView> GetActiveTicketServiceViews(int employeeId)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                var query = db.InstantTicketServiceViews.Where(x=>x.InstantServiceStateId!=3);
                if (employeeId > 0)
                {
                    query = query.Where(x => x.EmployeeId == employeeId);
                }
                return query.ToList();
            }
        }

        public List<InstantTicketProductView> GetActiveTicketProductViews(int employeeId)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                var query = db.InstantTicketProductViews.Where(x => x.InstantProductStateId != 3);
                if (employeeId > 0)
                {
                    query = query.Where(x => x.EmployeeId == employeeId);
                }
                return query.ToList();
            }
        }

        public void UpdateTicketProductStatus(long instantProductId, int statusId, string description, string note)
        {
            using (var db = new MonkeyWarrantyEntities())
            {
                db.InstantTicket_UpdateProductStatus(instantProductId, statusId, description, note);
            }
        }

        #endregion

        #region Ticket Employee Methods

        public InstantTicket_Employee CreateTicketEmployee(InstantTicket_Employee ticketEmployee)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                context.InstantTickets_Employees.AddObject(ticketEmployee);
                context.SaveChanges();
            }

            return ticketEmployee;
        }

        public List<InstantTicketEmployeeView> GetTicketEmployees(long ticketId)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                return context.InstantTicketEmployeeViews.Where(x => x.InstantTicketId == ticketId).ToList();
            }
        }

        public void DeleteTicketEmployee(long ticketEmployeeId)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                var ticketEmployee = context.InstantTickets_Employees.FirstOrDefault(x => x.Id == ticketEmployeeId);
                if (ticketEmployee == null) return;
                context.DeleteObject(ticketEmployee);
                context.SaveChanges();
            }
        }

        public InstantTicket_Employee GetTicketEmployeeById(long id)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                return context.InstantTickets_Employees.FirstOrDefault(x => x.Id == id);
            }
        }

        public void UpdateTicketEmployee(InstantTicket_Employee ticketEmployee)
        {
            if (ticketEmployee == null || ticketEmployee.Id == 0) return;

            using (var context = new MonkeyWarrantyEntities())
            {
                context.InstantTickets_Employees.Attach(ticketEmployee);
                context.ObjectStateManager.ChangeObjectState(ticketEmployee, EntityState.Modified);
                context.SaveChanges();
            }
        }

        #endregion

        #region Printing Methods

        public InstantTicketPrintBo GetInstantTicketForPrint(long id)
        {
            InstantTicketPrintBo instantTicketPrintBo = null;

            using (var context = new MonkeyWarrantyEntities())
            {
                var query = from ticket in context.InstantTickets
                            join customer in context.Customers on ticket.CustomerId equals customer.Id
                            join employee in context.Employees on ticket.CreatedEmployeeId equals employee.Id
                            where ticket.Id == id
                            select new { Ticket = ticket, CreatedEmployee = employee, Customer = customer };
                var item = query.FirstOrDefault();

                if (item != null)
                {
                    instantTicketPrintBo = new InstantTicketPrintBo
                                               {
                                                   InstantTicketId = id,
                                                   InstantTicketCode = item.Ticket.Code,
                                                   InstantTicketContent = item.Ticket.TicketContent,
                                                   InstantTicketDate = item.Ticket.CreatedDate,
                                                   CustomerName = item.Customer.Name,
                                                   CustomerAddress = item.Customer.Address,
                                                   CustomerPhone = item.Customer.Phone,
                                                   Employee1 = item.CreatedEmployee.Name
                                               };
                }
            }

            return instantTicketPrintBo;
        }

        public List<TaskServiceBo> GetInstantTicketTaskServiceDetails(long id)
        {
            var ticketDetails = new List<TaskServiceBo>();

            using (var context = new MonkeyWarrantyEntities())
            {
                var index = 0;
                // get products
                var query = from tp in context.InstantTickets_Products
                            join p in context.Products on tp.ProductId equals p.Id into list1
                            join e in context.Errors on tp.ErrorId equals e.Id into list2
                            join wt in context.WarrantyTypes on tp.WarrantyTypeId equals wt.Id into list3
                            where tp.InstantTicketId == id

                            from l1 in list1.DefaultIfEmpty()
                            from l2 in list2.DefaultIfEmpty()
                            from l3 in list3.DefaultIfEmpty()
                            select new
                            {
                                tp.Quantity,
                                tp.WarrantyDate,
                                ProductName = l1.Name,
                                ErrorName = l2.Name,
                                TypeName = l3.Name
                            };

                var ticketProductDetails = query.ToList();

                foreach (var ticketProductDetail in ticketProductDetails)
                {
                    index++;
                    ticketDetails.Add(new TaskServiceBo
                                          {
                                              OrderNumber = index,
                                              Name = ticketProductDetail.ProductName,
                                              Quantity = ticketProductDetail.Quantity,
                                              WarrantyDate = ticketProductDetail.WarrantyDate,
                                              ErrorName = ticketProductDetail.ErrorName,
                                              TypeName = ticketProductDetail.TypeName
                                          });
                }

                // get services
                var query1 = from ts in context.InstantTickets_Services
                             join s in context.Services on ts.ServiceId equals s.Id into list1
                             join e in context.Errors on ts.ErrorId equals e.Id into list2
                             join st in context.ServiceTypes on ts.ServiceTypeId equals st.Id into list3
                             where ts.InstantTicketId == id

                             from l1 in list1.DefaultIfEmpty()
                             from l2 in list2.DefaultIfEmpty()
                             from l3 in list3.DefaultIfEmpty()
                             select new
                             {
                                 ts.Quantity,
                                 ProductName = l1.Name,
                                 ErrorName = l2.Name,
                                 TypeName = l3.Name
                             };
                var ticketServiceDetails = query1.ToList();
                foreach (var ticketServiceDetail in ticketServiceDetails)
                {
                    index++;
                    ticketDetails.Add(new TaskServiceBo
                    {
                        OrderNumber = index,
                        Name = ticketServiceDetail.ProductName,
                        Quantity = ticketServiceDetail.Quantity,
                        ErrorName = ticketServiceDetail.ErrorName,
                        TypeName = ticketServiceDetail.TypeName
                    });
                }
            }

            return ticketDetails;
        }

        #endregion

        #region Reporting Methods

        public List<InstantTicketEmployeeCount> GetInstantTicketEmployeeCount(DateTime dateFrom, DateTime dateTo)
        {
            using (var context = new MonkeyWarrantyEntities())
            {
                return context.InstantTicketEmployeeCount(dateFrom, dateTo).ToList();
            }
        }

        #endregion

        #region Helpers
        #endregion
    }
}
